package edu.byu.intex2.data.dao;

import edu.byu.intex2.data.Cache;
import edu.byu.intex2.data.ConnectionPool;
import edu.byu.intex2.data.DataException;
import edu.byu.intex2.data.bo.Employee;
import edu.byu.intex2.data.bo.Salesperson;
import java.sql.*;
import java.util.List;
import java.util.LinkedList;

/**
 * A singleton object that CRUD's Salesperson objects.
 *
 * @author Brady White <satara@gmail.com>
 * @version 2009-02-13
 */
public class SalespersonDAO {

    /////////////////////////////////////////////
    ///   Singleton code
    private static SalespersonDAO instance = null;

    /** Creates a new instance of SalespersonDAO */
    private SalespersonDAO() {
    }//constructor

    /** Retrieves the single instance of this class */
    public static synchronized SalespersonDAO getInstance() {
        if (instance == null) {
            instance = new SalespersonDAO();
        }
        return instance;
    }//getInstance

    ////////////////////////////////////////////
    ///   CREATE methods
    /** Creates a new salesperson in the database */
    public Salesperson create(String id) throws DataException {
        // Create new Salesperson with GUID
        Salesperson salesperson = new Salesperson(id);
        salesperson.setObjectAlreadyInDB(false); // set alreadyInDB to false

        // put salesperson in cache
        Cache.getInstance().put(salesperson.getId(), salesperson);
        return salesperson;
    }

    ////////////////////////////////////////////
    ///   READ methods
    /** Reads an existing salesperson from the database */
    public Salesperson read(String id) throws DataException {
        // Check Cache
        if (Cache.getInstance().containsKey(id)) {
            return (Salesperson) Cache.getInstance().get(id);
        }

        // Get Connection
        Connection conn = ConnectionPool.getInstance().get();

        // Try Reading with connection
        try {
            return read(id, conn);
        } catch (Exception e) {
            throw new DataException("An error occurred while reading the business object information.", e);
        } finally {
            ConnectionPool.getInstance().release(conn);
        }
    }

    /** Internal method to read an existing salesperson from the database */
    synchronized Salesperson read(String id, Connection conn) throws Exception {
        //check the cache just like above just to make sure
        if (Cache.getInstance().containsKey(id)) {
            return (Salesperson) Cache.getInstance().get(id);
        }

        // Prepare the SQL statement
        PreparedStatement stmt = conn.prepareStatement("SELECT * FROM salesperson where id=?");
        try {
            stmt.setString(1, id);
            ResultSet rs = stmt.executeQuery(); // run sql
            if (rs.next()) {
                return readRecord(rs, conn);
            }//if
            throw new DataException("Salesperson with id '" + id + "' not found.");
        } finally {
            stmt.close();
        }
    }//read

    /** Internal method to create a salesperson object from a record */
    synchronized Salesperson readRecord(ResultSet rs, Connection conn) throws Exception {
        //again, check the cache, but check it with the id from the record set that was passed
        if (Cache.getInstance().containsKey(rs.getString("id"))) {
            return (Salesperson) Cache.getInstance().get(rs.getString("id"));
        }

        // Create the salesperson since we couldn't find it in cache
        Salesperson salesperson = new Salesperson(rs.getString("id"));

        // Object was found in database
        salesperson.setObjectAlreadyInDB(true);

        // Set all salesperson attributes from result set
        salesperson.setCommissionPercent(rs.getDouble("commission_percent"));

        // Since we inherit from Employee, set all the respective attributes of employee and person
        EmployeeDAO.getInstance().setEmpValues(salesperson, conn);

        // Object isn't dirty, we haven't updated it
        salesperson.setDirty(false);

        // Put salesperson in cache
        Cache.getInstance().put(salesperson.getId(), salesperson);

        return salesperson;

    }//readRecord

    /////////////////////////////////////////////
    ///   UPDATE methods
    /** Saves an existing salesperson in the database */
    public void save(Salesperson salesperson) throws DataException {
        // get a connection from the pool, save all of the sales first using the other employee method and then commit them in the database
        Connection conn = ConnectionPool.getInstance().get();

        try {
            save(salesperson, conn); // save with connection
            conn.commit();  // commit the db transaction
        } catch (Exception e) {
            try {
                //rollback if there were any errors saving, catch any other errors you might have
                conn.rollback();
            } catch (SQLException e2) {
                throw new DataException("Could not roll back the salesperson database transaction!", e2);
            }
            throw new DataException("An error occurred while saving the salesperson object information.", e);
        } finally {
            // release the connection
            ConnectionPool.getInstance().release(conn);
        }
    }

    /** Internal method to update a salesperson in the database */
    void save(Salesperson salesperson, Connection conn) throws Exception {
        // put the employee object to cache
        Cache.getInstance().put(salesperson.getId(), salesperson);

        // decide whether to update or insert into the database
        if (salesperson.isObjectAlreadyInDB() && salesperson.isDirty()) {
            // Update Employee
            EmployeeDAO.getInstance().saveNoCommit(salesperson, conn, salesperson.isObjectAlreadyInDB());

            // Update salesperson
            update(salesperson, conn);
        } else if (salesperson.isDirty()) {
            // Insert Employee
            EmployeeDAO.getInstance().saveNoCommit(salesperson, conn, salesperson.isObjectAlreadyInDB());

            // Insert Customer
            insert(salesperson, conn);
        }//if
    }

    /** Saves an existing salesperson to the database */
    private void update(Salesperson salesperson, Connection conn) throws Exception {
        // use a prepared statement to prevent sql injection
        PreparedStatement stmt = conn.prepareStatement("UPDATE salesperson SET commission_percent=?  WHERE id=?");
        try {
            // Get Person Attributes and add them to the statement
            stmt.setDouble(1, salesperson.getCommissionPercent());
            stmt.setString(2, salesperson.getId());
            stmt.execute();

            // Person is clean!
            salesperson.setDirty(false);
        } finally {
            stmt.close();  // make sure to close the prepared statement
        }
    }

    /** Inserts a new salesperson into the database */
    private void insert(Salesperson salesperson, Connection conn) throws Exception {
        PreparedStatement stmt = conn.prepareStatement("INSERT INTO salesperson (id, commission_percent) VALUES (?, ?)");
        try {
            // Set Salesperson Attributes
            stmt.setString(1, salesperson.getId());
            stmt.setDouble(2, salesperson.getCommissionPercent());
            stmt.execute();

            salesperson.setObjectAlreadyInDB(true); // now in DB
            salesperson.setDirty(false); // object is clean
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            stmt.close(); // close the prepared statement
        }
    }

    /////////////////////////////////////////////////
    ///   DELETE methods
    /** We do not support deleting of business objects in this application */
    public void delete(Salesperson salesperson) throws DataException {
        throw new RuntimeException("Nice try. The delete function is not supported in this application.");
    }

    ////////////////////////////////////////////////
    ///   SEARCH methods
    /** Retrieves all dayofweeks from the database */
    public List<Salesperson> getAll() throws DataException {
        //establish a connection from the pool, use prepared statement, use the search method below to run the statement, catch any errors
        //release the connection
        return null;

    }

    /** Internal method to search by certain fields */
    private List<Salesperson> search(PreparedStatement stmt, Connection conn) throws Exception {
        //instantiate a list of sales to add recordsets to. this will make going through the list easier
        //finally, close the statement and return the list
        return null;

    }
    /*this is in a list because we may have more than one salesperson with the same name, just allow the user to pick one since it is an exception*/

    public List<Salesperson> getByName(String fname, String lname) throws DataException {
        //establish connection, prepared statement call the search
        return null;
    }

    public Salesperson getBylength(int months) throws DataException {
        //establish connection, prepared statement call the search
        //call the read(rs.GUID) and return the result
        return null;
    }
}//class
